﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace exceltodata
{
    public partial class ExcelToByte : Form
    {
        private byte[] xorScale = new byte[] { 45,66,38,55,23,254,9,165,90,19,41,45,201,58,55,37,254,185,165,169,19,171};
        public ExcelToByte()
        {
            InitializeComponent();
        }

        #region 生成Data文件
        /// <summary>
        /// 选择表格文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void selectBtn_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.txtFilePath.Text = "";
                foreach (string strName in this.openFileDialog.FileNames)
                {
                    this.txtFilePath.Text += strName + "\r\n";
                }
            }
        }
        /// <summary>
        /// 创建流文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void createBtn_Click(object sender, EventArgs e)
        {
            try
            {
                string[] arr = this.txtFilePath.Text.Trim().Split('\r', '\n');
                if (arr.Length > 0)
                {
                    for (int i = 0; i < arr.Length; i++)
                    {
                        ReadData(arr[i]);
                    }
                    MessageBox.Show("创建成功");
                }
                else {
                    MessageBox.Show("创建失败，请选择文件路径");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("创建失败 " + ex.Message);
            }
        }
        /// <summary>
        /// 读取表格
        /// </summary>
        /// <param name="path"></param>
        private void ReadData(string path)
        {
            if (string.IsNullOrEmpty(path)) return;
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            DataTable dt = null;
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                string tableName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = string.Format("select * from [{0}]", tableName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
                dt = ds.Tables[0];
                myCommand.Dispose();
            }

            CreateData(path, dt);
        }
        
        /// <summary>
        /// 创建流数据
        /// </summary>
        /// <param name="path"></param>
        /// <param name="dt"></param>
        private void CreateData(string path, DataTable dt)
        {
            string filePath = path.Substring(0, path.LastIndexOf('\\') + 1);
            string fileFullName = path.Substring(path.LastIndexOf('\\') + 1);
            string fileName = fileFullName.Substring(0, fileFullName.LastIndexOf('.'));

            byte[] buffer = null;
            string[,] dataArr = null;
            using (MMO_MemoryStream ms = new MMO_MemoryStream())
            {
                int row = dt.Rows.Count;
                int columns = dt.Columns.Count;

                dataArr = new string[columns, 3];

                ms.WriteInt(row);
                ms.WriteInt(columns);
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < columns; j++)
                    {
                        if (i < 3) {
                            dataArr[j, i] = dt.Rows[i][j].ToString().Trim();
                        }
                        ms.WriteUTF8String(dt.Rows[i][j].ToString().Trim());
                    }
                }
                buffer = ms.ToArray();
            }
            //加密
            int iScaleLen = xorScale.Length;
            for (int i = 0; i < buffer.Length; i++)
            {
                buffer[i] = (byte)(buffer[i] ^ xorScale[i % iScaleLen]);
            }
            //压缩

            //写入文件
            FileStream fs = new FileStream(string.Format("{0}{1}", filePath, fileName + ".data"), FileMode.Create);
            fs.Write(buffer, 0, buffer.Length);
            fs.Close();

            CreateDBModel(filePath, fileName, dataArr);
            CreateEntity(filePath, fileName, dataArr);
        }

        /// <summary>
        /// 创建实体
        /// </summary>
        private void CreateEntity(string filePath, string fileName,string[,] dataArr) {
            if (dataArr == null) return;

            if (!Directory.Exists(string.Format("{0}Create", filePath))) {
                Directory.CreateDirectory(string.Format("{0}Create", filePath));
            }

            StringBuilder sbr = new StringBuilder();
            sbr.Append("\r\n");
            sbr.AppendFormat("// 创建时间：{0}\r\n", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            sbr.Append("/// <summary>\r\n");
            sbr.AppendFormat("/// {0}商品实体\r\n",fileName);
            sbr.Append("/// </summary>\r\n");
            sbr.AppendFormat("public partial class {0}Entity : AbstractEntity\r\n", fileName);
            sbr.Append("{\r\n");

            for (int i = 0; i < dataArr.GetLength(0); i++) {
                if (i == 0) continue;
                sbr.Append("   /// <summary>\r\n");
                sbr.AppendFormat("   /// {0}\r\n", dataArr[i,2]);
                sbr.Append("   /// </summary>\r\n");
                sbr.AppendFormat("   public {0} {1} {{ get; set; }}\r\n", dataArr[i,1], dataArr[i, 0]);
                sbr.Append("\r\n");
            }

            sbr.Append("}\r\n");
            sbr.Append("\r\n");

            using (FileStream fs = new FileStream(string.Format("{0}Create/{1}Entity.cs", filePath, fileName), FileMode.Create))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.Write(sbr.ToString());
                }
            }

         }

        /// <summary>
        /// 创建数据管理类
        /// </summary>
        private void CreateDBModel(string filePath, string fileName,string[,] dataArr) {
            if (dataArr == null) return;

            if (!Directory.Exists(string.Format("{0}Create", filePath)))
            {
                Directory.CreateDirectory(string.Format("{0}Create", filePath));
            }

            StringBuilder sbr = new StringBuilder();
            sbr.Append("\r\n");
            sbr.AppendFormat("// 创建时间：{0}\r\n", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            sbr.Append("using System;\r\n");
            sbr.Append("using System.Collections.Generic;\r\n");
            sbr.Append("\r\n");
            sbr.Append("/// <summary>\r\n");
            sbr.AppendFormat("/// {0}数据管理\r\n", fileName);
            sbr.Append("/// </summary>\r\n");
            sbr.AppendFormat("public partial class {0}DBModel : AbstractDBModel<{0}DBModel, {0}Entity>\r\n", fileName);
            sbr.Append("{\r\n");
            sbr.Append("   /// <summary>\r\n");
            sbr.Append("   /// 文件名称\r\n");
            sbr.Append("   /// </summary>\r\n");
            sbr.AppendFormat("   protected override string FileName {{ get {{ return \"{0}.data\"; }} }}\r\n", fileName);
            sbr.Append("\r\n");
            sbr.Append("   /// <summary>\r\n");
            sbr.Append("   /// 创建实体\r\n");
            sbr.Append("   /// </summary>\r\n");
            sbr.Append("   /// <param name=\"parser\"></param>\r\n");
            sbr.Append("   /// <returns></returns>\r\n");
            sbr.AppendFormat("   protected override {0}Entity MakeEntity(GameDataTableParser parser)\r\n", fileName);
            sbr.Append("   {\r\n");
            sbr.AppendFormat("      {0}Entity entity = new {0}Entity();\r\n", fileName);

            for (int i = 0; i < dataArr.GetLength(0); i++) {
                sbr.AppendFormat("      entity.{0} = parser.GetFieldValue(\"{0}\"){1};\r\n", dataArr[i,0],ChangeTypeName(dataArr[i, 1]));
            }

            sbr.Append("      return entity;\r\n");
            sbr.Append("   }\r\n");
            sbr.Append("}\r\n");

            using (FileStream fs = new FileStream(string.Format("{0}Create/{1}DBModel.cs", filePath, fileName), FileMode.Create))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.Write(sbr.ToString());
                }
            }
        }

        private string ChangeTypeName(string type) {
            string str = string.Empty;
            switch (type) {
                case "int":
                    str = ".ToInt()";
                    break;
                case "long":
                    str = ".ToLong()";
                    break;
                case "float":
                    str = ".ToFloat()";
                    break;
            }
            return str;
        }

        /// <summary>
        /// 获取表格的第一个数据表名称
        /// </summary>
        /// <param name="path"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        private string GetFirstSheetNameFromExcelFileName(string path, int index)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;imex=1;hdr=no\";";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            return dtSheetName.Rows[index-1]["TABLE_NAME"].ToString();
        }
        #endregion

        #region 解密data文件
        /// <summary>
        /// 选择data文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void selectDataBtn_Click(object sender, EventArgs e)
        {
            if (this.openDataFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                this.txtFileDataPath.Text = "";
                string strPath = this.openDataFileDialog.FileName;

                using (GameDataTableParser parse = new GameDataTableParser(strPath)) {
                    while (!parse.Eof) {
                        StringBuilder sbr = new StringBuilder();
                        for (int i = 0; i < parse.FileName.Length; i++) {
                            sbr.AppendFormat("{0}", parse.GetFieldValue(parse.FileName[i]));
                        }
                        this.txtFileDataPath.Text += sbr.ToString() + "\r\n";
                        parse.Next();
                    }
                }
            }
        } 
        #endregion
    }
}
